import datetime
from typing import List, Optional

from sqlalchemy import (
    CHAR,
    BigInteger,
    CheckConstraint,
    Column,
    DateTime,
    ForeignKeyConstraint,
    Identity,
    Index,
    Integer,
    PrimaryKeyConstraint,
    String,
    Text,
    UniqueConstraint,
    text,
)
from sqlalchemy.orm import relationship as sa_relationship
from sqlmodel import Field, Relationship, SQLModel


class SysDept(SQLModel, table=True):
    __tablename__ = "sys_dept"
    __table_args__ = (
        CheckConstraint("id <> parent_id", name="chk_dept_no_self_parent"),
        CheckConstraint("status IN (0, 1)", name="chk_dept_status"),
        CheckConstraint("is_deleted IN (0, 1)", name="chk_dept_is_deleted"),
        ForeignKeyConstraint(["parent_id"], ["sys_dept.id"], ondelete="RESTRICT", name="fk_dept_parent"),
        PrimaryKeyConstraint("id", name="sys_dept_pkey"),
        Index("uk_dept_code", "code", unique=True),
        {"comment": "部门表"},
    )

    id: int = Field(
        sa_column=Column(
            "id",
            BigInteger,
            Identity(start=1, increment=1, minvalue=1, cycle=False, cache=1),
            primary_key=True,
            comment="主键",
        )
    )
    code: str = Field(sa_column=Column("code", String(100), nullable=False, comment="部门编号"))
    name: str = Field(sa_column=Column("name", String(100), nullable=False, comment="部门名称"))
    tree_path: str = Field(sa_column=Column("tree_path", String(255), nullable=False, comment="父节点id路径"))
    parent_id: Optional[int] = Field(default=None, sa_column=Column("parent_id", BigInteger, comment="父节点id"))
    sort: Optional[int] = Field(
        default=None, sa_column=Column("sort", Integer, server_default=text("0"), comment="显示顺序")
    )
    status: Optional[int] = Field(
        default=None, sa_column=Column("status", Integer, server_default=text("1"), comment="状态(1-正常 0-禁用)")
    )
    create_by: Optional[int] = Field(default=None, sa_column=Column("create_by", BigInteger, comment="创建人ID"))
    create_time: Optional[datetime.datetime] = Field(
        default=None,
        sa_column=Column("create_time", DateTime, server_default=text("CURRENT_TIMESTAMP"), comment="创建时间"),
    )
    update_by: Optional[int] = Field(default=None, sa_column=Column("update_by", BigInteger, comment="修改人ID"))
    update_time: Optional[datetime.datetime] = Field(
        default=None, sa_column=Column("update_time", DateTime, comment="更新时间")
    )
    is_deleted: Optional[int] = Field(
        default=None,
        sa_column=Column("is_deleted", Integer, server_default=text("0"), comment="逻辑删除标识(0-未删除 1-已删除)"),
    )

    parent: Optional["SysDept"] = Relationship(
        sa_relationship=sa_relationship("SysDept", remote_side="[SysDept.id]", back_populates="children")
    )
    children: List["SysDept"] = Relationship(back_populates="parent")
    users: List["SysUser"] = Relationship(back_populates="dept")


class SysLog(SQLModel, table=True):
    __tablename__ = "sys_log"
    __table_args__ = (
        PrimaryKeyConstraint("id", name="sys_log_pkey"),
        Index("idx_log_create_time", "create_time"),
        {"comment": "系统日志表"},
    )

    id: int = Field(
        sa_column=Column(
            "id",
            BigInteger,
            Identity(start=1, increment=1, minvalue=1, cycle=False, cache=1),
            primary_key=True,
            comment="主键",
        )
    )
    module: str = Field(sa_column=Column("module", String(50), nullable=False, comment="日志模块"))
    request_method: str = Field(sa_column=Column("request_method", String(64), nullable=False, comment="请求方式"))
    content: str = Field(sa_column=Column("content", String(255), nullable=False, comment="日志内容"))
    request_params: Optional[str] = Field(
        default=None, sa_column=Column("request_params", Text, comment="请求参数(批量请求参数可能会超过text)")
    )
    response_content: Optional[str] = Field(
        default=None, sa_column=Column("response_content", Text, comment="返回参数")
    )
    request_uri: Optional[str] = Field(default=None, sa_column=Column("request_uri", String(255), comment="请求路径"))
    method: Optional[str] = Field(default=None, sa_column=Column("method", String(255), comment="方法名"))
    ip: Optional[str] = Field(default=None, sa_column=Column("ip", String(45), comment="IP地址"))
    province: Optional[str] = Field(default=None, sa_column=Column("province", String(100), comment="省份"))
    city: Optional[str] = Field(default=None, sa_column=Column("city", String(100), comment="城市"))
    execution_time: Optional[int] = Field(
        default=None, sa_column=Column("execution_time", BigInteger, comment="执行时间(ms)")
    )
    browser: Optional[str] = Field(default=None, sa_column=Column("browser", String(100), comment="浏览器"))
    browser_version: Optional[str] = Field(
        default=None, sa_column=Column("browser_version", String(100), comment="浏览器版本")
    )
    os: Optional[str] = Field(default=None, sa_column=Column("os", String(100), comment="终端系统"))
    create_by: Optional[int] = Field(default=None, sa_column=Column("create_by", BigInteger, comment="创建人ID"))
    create_time: Optional[datetime.datetime] = Field(
        default=None,
        sa_column=Column("create_time", DateTime, server_default=text("CURRENT_TIMESTAMP"), comment="创建时间"),
    )
    is_deleted: Optional[int] = Field(
        default=None,
        sa_column=Column("is_deleted", Integer, server_default=text("0"), comment="逻辑删除标识(0-未删除 1-已删除)"),
    )


class SysRoleMenu(SQLModel, table=True):
    __tablename__ = "sys_role_menu"
    __table_args__ = (
        ForeignKeyConstraint(["menu_id"], ["sys_menu.id"], ondelete="RESTRICT", name="fk_role_menu_menuid"),
        ForeignKeyConstraint(["role_id"], ["sys_role.id"], ondelete="RESTRICT", name="fk_role_menu_roleid"),
        PrimaryKeyConstraint("role_id", "menu_id", name="sys_role_menu_pkey"),
        {"comment": "角色和菜单关联表"},
    )
    role_id: int = Field(sa_column=Column("role_id", BigInteger, comment="角色ID"))
    menu_id: int = Field(sa_column=Column("menu_id", BigInteger, comment="菜单ID"))


class SysUserRole(SQLModel, table=True):
    __tablename__ = "sys_user_role"
    __table_args__ = (
        ForeignKeyConstraint(["role_id"], ["sys_role.id"], ondelete="RESTRICT", name="fk_user_role_roleid"),
        ForeignKeyConstraint(["user_id"], ["sys_user.id"], ondelete="RESTRICT", name="fk_user_role_userid"),
        PrimaryKeyConstraint("user_id", "role_id", name="sys_user_role_pkey"),
        {"comment": "用户和角色关联表"},
    )
    user_id: int = Field(sa_column=Column("user_id", BigInteger, comment="用户ID"))
    role_id: int = Field(sa_column=Column("role_id", BigInteger, comment="角色ID"))


class SysMenu(SQLModel, table=True):
    __tablename__ = "sys_menu"
    __table_args__ = (
        CheckConstraint("id <> parent_id", name="chk_menu_no_self_parent"),
        CheckConstraint("type IN (1, 2, 3, 4)", name="chk_menu_type"),
        CheckConstraint("visible IN (0, 1)", name="chk_menu_visible"),
        CheckConstraint("always_show IN (0, 1)", name="chk_menu_always_show"),
        CheckConstraint("keep_alive IN (0, 1)", name="chk_menu_keep_alive"),
        ForeignKeyConstraint(["parent_id"], ["sys_menu.id"], ondelete="RESTRICT", name="fk_menu_parent"),
        PrimaryKeyConstraint("id", name="sys_menu_pkey"),
        Index("idx_menu_parent_id", "parent_id"),
        Index("idx_menu_type", "type"),
        {"comment": "菜单管理"},
    )

    id: int = Field(
        sa_column=Column(
            "id",
            BigInteger,
            Identity(start=1, increment=1, minvalue=1, cycle=False, cache=1),
            primary_key=True,
            comment="ID",
        )
    )
    name: str = Field(sa_column=Column("name", String(64), nullable=False, comment="菜单名称"))
    type: int = Field(
        sa_column=Column("type", Integer, nullable=False, comment="菜单类型（1-菜单 2-目录 3-外链 4-按钮）")
    )
    parent_id: Optional[int] = Field(default=None, sa_column=Column("parent_id", BigInteger, comment="父菜单ID"))
    tree_path: str = Field(sa_column=Column("tree_path", String(255), nullable=False, comment="父节点id路径"))
    route_name: Optional[str] = Field(
        default=None, sa_column=Column("route_name", String(255), comment="路由名称（Vue Router 中用于命名路由）")
    )
    route_path: Optional[str] = Field(
        default=None, sa_column=Column("route_path", String(128), comment="路由路径（Vue Router 中定义的 URL 路径）")
    )
    component: Optional[str] = Field(
        default=None,
        sa_column=Column(
            "component", String(128), comment="组件路径（组件页面完整路径，相对于 src/views/，缺省后缀 .vue）"
        ),
    )
    perm: Optional[str] = Field(default=None, sa_column=Column("perm", String(128), comment="【按钮】权限标识"))
    always_show: Optional[int] = Field(
        default=None,
        sa_column=Column(
            "always_show", Integer, server_default=text("0"), comment="【目录】只有一个子路由是否始终显示（1-是 0-否）"
        ),
    )
    keep_alive: Optional[int] = Field(
        default=None,
        sa_column=Column(
            "keep_alive", Integer, server_default=text("0"), comment="【菜单】是否开启页面缓存（1-是 0-否）"
        ),
    )
    visible: Optional[int] = Field(
        default=None,
        sa_column=Column("visible", Integer, server_default=text("1"), comment="显示状态（1-显示 0-隐藏）"),
    )
    sort: Optional[int] = Field(
        default=None, sa_column=Column("sort", Integer, server_default=text("0"), comment="排序")
    )
    icon: Optional[str] = Field(default=None, sa_column=Column("icon", String(64), comment="菜单图标"))
    redirect: Optional[str] = Field(default=None, sa_column=Column("redirect", String(128), comment="跳转路径"))
    create_time: Optional[datetime.datetime] = Field(
        default=None,
        sa_column=Column("create_time", DateTime, server_default=text("CURRENT_TIMESTAMP"), comment="创建时间"),
    )
    update_time: Optional[datetime.datetime] = Field(
        default=None, sa_column=Column("update_time", DateTime, comment="更新时间")
    )
    params: Optional[str] = Field(default=None, sa_column=Column("params", String(255), comment="路由参数"))

    parent: Optional["SysMenu"] = Relationship(
        sa_relationship=sa_relationship("SysMenu", remote_side="[SysMenu.id]", back_populates="children")
    )  # 指定“远程”是 id（即父节点的 id）
    children: List["SysMenu"] = Relationship(back_populates="parent")
    roles: List["SysRole"] = Relationship(back_populates="menus", link_model=SysRoleMenu)


class SysRole(SQLModel, table=True):
    __tablename__ = "sys_role"
    __table_args__ = (
        CheckConstraint("status IN (0, 1)", name="chk_role_status"),
        CheckConstraint("data_scope IN (1, 2, 3, 4)", name="chk_role_data_scope"),
        CheckConstraint("is_deleted IN (0, 1)", name="chk_role_is_deleted"),
        PrimaryKeyConstraint("id", name="sys_role_pkey"),
        Index("uk_role_code", "code", unique=True),
        Index("uk_role_name", "name", unique=True),
        {"comment": "角色表"},
    )

    id: int = Field(
        sa_column=Column(
            "id",
            BigInteger,
            Identity(start=1, increment=1, minvalue=1, cycle=False, cache=1),
            primary_key=True,
        )
    )
    name: str = Field(sa_column=Column("name", String(64), nullable=False, comment="角色名称"))
    code: str = Field(sa_column=Column("code", String(32), nullable=False, comment="角色编码"))
    sort: Optional[int] = Field(default=None, sa_column=Column("sort", Integer, comment="显示顺序"))
    status: Optional[int] = Field(
        default=None, sa_column=Column("status", Integer, server_default=text("1"), comment="角色状态(1-正常 0-停用)")
    )
    data_scope: Optional[int] = Field(
        default=None,
        sa_column=Column(
            "data_scope", Integer, comment="数据权限(1-所有数据 2-部门及子部门数据 3-本部门数据 4-本人数据)"
        ),
    )
    create_by: Optional[int] = Field(default=None, sa_column=Column("create_by", BigInteger, comment="创建人 ID"))
    create_time: Optional[datetime.datetime] = Field(
        default=None,
        sa_column=Column("create_time", DateTime, server_default=text("CURRENT_TIMESTAMP"), comment="创建时间"),
    )
    update_by: Optional[int] = Field(default=None, sa_column=Column("update_by", BigInteger, comment="更新人ID"))
    update_time: Optional[datetime.datetime] = Field(
        default=None, sa_column=Column("update_time", DateTime, comment="更新时间")
    )
    is_deleted: Optional[int] = Field(
        default=None,
        sa_column=Column("is_deleted", Integer, server_default=text("0"), comment="逻辑删除标识(0-未删除 1-已删除)"),
    )

    menus: List["SysMenu"] = Relationship(back_populates="roles", link_model=SysRoleMenu)
    users: List["SysUser"] = Relationship(back_populates="roles", link_model=SysUserRole)


class SysUser(SQLModel, table=True):
    __tablename__ = "sys_user"
    __table_args__ = (
        CheckConstraint("gender IN (0, 1, 2)", name="chk_user_gender"),
        CheckConstraint("status IN (0, 1)", name="chk_user_status"),
        CheckConstraint("is_deleted IN (0, 1)", name="chk_user_is_deleted"),
        ForeignKeyConstraint(["dept_id"], ["sys_dept.id"], ondelete="RESTRICT", name="fk_user_dept"),
        PrimaryKeyConstraint("id", name="sys_user_pkey"),
        Index("idx_user_username", "username"),
        {"comment": "用户信息表"},
    )

    id: int = Field(
        sa_column=Column(
            "id",
            BigInteger,
            Identity(start=1, increment=1, minvalue=1, cycle=False, cache=1),
            primary_key=True,
        )
    )
    username: Optional[str] = Field(default=None, sa_column=Column("username", String(64), comment="用户名"))
    nickname: Optional[str] = Field(default=None, sa_column=Column("nickname", String(64), comment="昵称"))
    gender: Optional[int] = Field(
        default=None, sa_column=Column("gender", Integer, server_default=text("1"), comment="性别(1-男 2-女 0-保密)")
    )
    password: Optional[str] = Field(default=None, sa_column=Column("password", String(100), comment="密码"))
    dept_id: Optional[int] = Field(default=None, sa_column=Column("dept_id", BigInteger, comment="部门ID"))
    avatar: Optional[str] = Field(default=None, sa_column=Column("avatar", String(255), comment="用户头像"))
    mobile: Optional[str] = Field(default=None, sa_column=Column("mobile", String(20), comment="联系方式"))
    status: Optional[int] = Field(
        default=None, sa_column=Column("status", Integer, server_default=text("1"), comment="状态(1-正常 0-禁用)")
    )
    email: Optional[str] = Field(default=None, sa_column=Column("email", String(128), comment="用户邮箱"))
    create_time: Optional[datetime.datetime] = Field(
        default=None,
        sa_column=Column("create_time", DateTime, server_default=text("CURRENT_TIMESTAMP"), comment="创建时间"),
    )
    create_by: Optional[int] = Field(default=None, sa_column=Column("create_by", BigInteger, comment="创建人ID"))
    update_time: Optional[datetime.datetime] = Field(
        default=None, sa_column=Column("update_time", DateTime, comment="更新时间")
    )
    update_by: Optional[int] = Field(default=None, sa_column=Column("update_by", BigInteger, comment="修改人ID"))
    is_deleted: Optional[int] = Field(
        default=None,
        sa_column=Column("is_deleted", Integer, server_default=text("0"), comment="逻辑删除标识(0-未删除 1-已删除)"),
    )
    openid: Optional[str] = Field(default=None, sa_column=Column("openid", CHAR(28), comment="微信 openid"))

    roles: List["SysRole"] = Relationship(back_populates="users", link_model=SysUserRole)
    dept: Optional["SysDept"] = Relationship(back_populates="users")


class SysDict(SQLModel, table=True):
    __tablename__ = "sys_dict"
    __table_args__ = (
        CheckConstraint("is_deleted IN (0, 1)", name="chk_dict_is_deleted"),
        CheckConstraint("status IN (0, 1)", name="chk_dict_status"),
        PrimaryKeyConstraint("id", name="sys_dict_pkey"),
        UniqueConstraint("dict_code", name="chk_dict_code"),
        {"comment": "字典表"},
    )

    id: int = Field(
        sa_column=Column(
            "id",
            BigInteger,
            Identity(start=1, increment=1, minvalue=1, cycle=False, cache=1),
            primary_key=True,
            comment="主键 ",
        )
    )
    dict_code: Optional[str] = Field(default=None, sa_column=Column("dict_code", String(50), comment="类型编码"))
    name: Optional[str] = Field(default=None, sa_column=Column("name", String(50), comment="类型名称"))
    status: Optional[int] = Field(
        default=None, sa_column=Column("status", Integer, server_default=text("0"), comment="状态(0:正常;1:禁用)")
    )
    remark: Optional[str] = Field(default=None, sa_column=Column("remark", String(255), comment="备注"))
    create_time: Optional[datetime.datetime] = Field(
        default=None, sa_column=Column("create_time", DateTime, comment="创建时间")
    )
    create_by: Optional[int] = Field(default=None, sa_column=Column("create_by", BigInteger, comment="创建人ID"))
    update_time: Optional[datetime.datetime] = Field(
        default=None, sa_column=Column("update_time", DateTime, comment="更新时间")
    )
    update_by: Optional[int] = Field(default=None, sa_column=Column("update_by", BigInteger, comment="修改人ID"))
    is_deleted: Optional[int] = Field(
        default=None,
        sa_column=Column("is_deleted", Integer, server_default=text("0"), comment="是否删除(1-删除，0-未删除)"),
    )

    items: list["SysDictItem"] = Relationship(back_populates="sys_dict")


class SysDictItem(SQLModel, table=True):
    __tablename__ = "sys_dict_item"
    __table_args__ = (
        CheckConstraint("status IN (0, 1)", name="chk_dict_item_status"),
        ForeignKeyConstraint(["dict_code"], ["sys_dict.dict_code"], ondelete="RESTRICT", name="fk_dict_code"),
        PrimaryKeyConstraint("id", name="sys_dict_item_pkey"),
        Index("idx_dict_item_code", "dict_code"),
        {"comment": "字典项表"},
    )

    id: int = Field(
        sa_column=Column(
            "id",
            BigInteger,
            Identity(start=1, increment=1, minvalue=1, cycle=False, cache=1),
            primary_key=True,
            comment="主键",
        )
    )
    dict_code: Optional[str] = Field(
        default=None, sa_column=Column("dict_code", String(50), comment="关联字典编码，与sys_dict表中的dict_code对应")
    )
    value: Optional[str] = Field(default=None, sa_column=Column("value", String(50), comment="字典项值"))
    label: Optional[str] = Field(default=None, sa_column=Column("label", String(100), comment="字典项标签"))
    tag_type: Optional[str] = Field(
        default=None,
        sa_column=Column("tag_type", String(50), comment="标签类型，用于前端样式展示（如success、warning等）"),
    )
    status: Optional[int] = Field(
        default=None, sa_column=Column("status", Integer, server_default=text("0"), comment="状态（1-正常，0-禁用）")
    )
    sort: Optional[int] = Field(
        default=None, sa_column=Column("sort", Integer, server_default=text("0"), comment="排序")
    )
    remark: Optional[str] = Field(default=None, sa_column=Column("remark", String(255), comment="备注"))
    create_time: Optional[datetime.datetime] = Field(
        default=None, sa_column=Column("create_time", DateTime, comment="创建时间")
    )
    create_by: Optional[int] = Field(default=None, sa_column=Column("create_by", BigInteger, comment="创建人ID"))
    update_time: Optional[datetime.datetime] = Field(
        default=None, sa_column=Column("update_time", DateTime, comment="更新时间")
    )
    update_by: Optional[int] = Field(default=None, sa_column=Column("update_by", BigInteger, comment="修改人ID"))

    sys_dict: Optional["SysDict"] = Relationship(back_populates="items")
